using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Reflection;
using DataObject.Core;
using DataObject.Database;

namespace DataObject.DataAccess
{
    public partial class CabinDAO : DataBaseProcessBase<CabinEntity, OBSDataContext>
    {
        #region CreateInstant
        public static CabinDAO CreateInstant()
        {
            return new CabinDAO();
        }
        #endregion

        public List<CabinEntity> GetAllCabins()
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.CabinEntities.ToList();
            }
        }

        public List<CabinEntity> FindCabin(int? CabinID, string BoatID, string CabinNumber, string Description, int? Floor, bool? ForRent, int? MaxGuests, bool? Active, HashSet<string> SortClause)
        {
            int Count = 0;
            return FindCabin(CabinID, BoatID, CabinNumber, Description, Floor, ForRent, MaxGuests, Active, null, null, out Count, SortClause);
        }

        public List<CabinEntity> FindCabin(int? CabinID, string BoatID, string CabinNumber, string Description, int? Floor, bool? ForRent, int? MaxGuests, bool? Active, int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = (from table in db.CabinEntities select table);
                if (CabinID.HasValue)
                    query = query.Where(table => table.CabinID == CabinID.Value);
                if (!BoatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(BoatID.ToLower()));
                if (!CabinNumber.Equals(string.Empty))
                    query = query.Where(table => table.CabinNumber.ToLower().Equals(CabinNumber.ToLower()));
                if (!Description.Equals(string.Empty))
                    query = query.Where(table => table.Description.ToLower().Equals(Description.ToLower()));
                if (Floor.HasValue)
                    query = query.Where(table => table.Floor == Floor.Value);
                if (ForRent.HasValue)
                    query = query.Where(table => table.ForRent == ForRent.Value);
                if (MaxGuests.HasValue)
                    query = query.Where(table => table.MaxGuests == MaxGuests.Value);
                if (Active.HasValue)
                    query = query.Where(table => table.Active == Active.Value);
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();
                if (Page.HasValue && Row.HasValue)
                    return query.PageIQuery(Page.Value, Row.Value).ToList();
                return query.ToList();
            }
        }

        public List<ViewCabinSatus> GetCabinStatus(string BoatID, DateTime date)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = (from table in db.ViewCabinSatus select table);
                query = query.Where(n => n.BoatID.ToLower().Equals(BoatID.ToUpper()) && n.Date.Value == date.Date);
                return query.ToList();
            }
        }

        public List<ViewCabinSatus> GetCabinStatus(bool Charter, string BoatId, DateTime CheckIn, int NumberDate, int NumberCabin)
        {
            List<ViewCabinSatus> result = new List<ViewCabinSatus>();
            BoatEntity boat = BoatDAO.CreateInstant().GetBoat(BoatId);
            int NumberCabinBoat = 0;
            if (Charter)
                NumberCabin = boat.NumOfCabins;
            NumberCabinBoat = boat.NumOfCabins;
            for (int i = 0; i < NumberDate; i++)
            {
                List<ViewCabinSatus> ls = CabinDAO.CreateInstant().GetCabinStatus(BoatId, CheckIn);
                if (ls != null && (NumberCabinBoat - ls.Count) >= NumberCabin)
                    result = ls;
                else
                    return null;
            }
            return result;
        }

        public List<ViewCabinBoat> GetCabinBoat(string BoatId)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.ViewCabinBoats.Where(n => n.BoatID.Equals(BoatId)).ToList();
            }
        }

        /// <summary>
        /// Get free cabin to create a reservation
        /// </summary>
        /// <param name="IsCharter"></param>
        /// <param name="BoatId"></param>
        /// <param name="CheckIn"></param>
        /// <param name="noOfNights"></param>
        /// <param name="NumberCabin"></param>
        /// <returns></returns>
        public List<ViewCabinBoat> GetCabinBoat(bool isCharter, string boatId, DateTime boardingDate, int noOfNights, int numOfCabins)
        {

            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var freeCabins = GetFreeCabins(db, boatId, boardingDate, noOfNights);

                if (isCharter)
                { numOfCabins = db.CabinEntities.Where(n => n.BoatID.Equals(boatId)).Count(); }

                if (freeCabins.Count() >= numOfCabins)
                    return freeCabins.OrderBy(x => x.CabinID).PageIQuery(1, numOfCabins).ToList();
            }
            
            return null;
        }

        /// <summary>
        /// Check available cabins
        /// </summary>
        /// <param name="boatId"></param>
        /// <param name="boardingDate"></param>
        /// <param name="noOfNights"></param>
        /// <param name="exceptCabins"></param>
        /// <returns></returns>
        public List<ViewCabinBoat> GetAvailableCabins(string boatId, DateTime boardingDate, int noOfNights, IList<string> exceptCabins)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var freeCabins = GetFreeCabins(db, boatId, boardingDate, noOfNights).ToList();

                if (freeCabins != null && freeCabins.Count > 0)
                {
                    return freeCabins.Where(t => !exceptCabins.Contains(t.CabinID.ToString())).ToList();
                }
                else
                    return new List<ViewCabinBoat>();
            }
        }

        /// <summary>
        /// Correct 100%
        /// </summary>
        /// <param name="boatId"></param>
        /// <param name="boardingDate"></param>
        /// <param name="noOfNights"></param>
        /// <returns></returns>
        private IQueryable<ViewCabinBoat> GetFreeCabins(OBSDataContext db, string boatId, DateTime boardingDate, int noOfNights)
        {
            IQueryable<ViewCabinBoat> freeCabins;

            // Get the cabin booked list from ... to boarding date
            var result_temp = from cs in db.CabinStatusEntities
                              join cb in db.CabinEntities on cs.CabinID equals cb.CabinID
                              where cs.Date.Date >= boardingDate.Date
                              && cs.Date.Date < boardingDate.Date.AddDays(noOfNights)
                              && cb.BoatID.Equals(boatId)

                              group cb.CabinID by cb.CabinID into gTemp
                              where gTemp.Count() > 0
                              select new { CabinID = gTemp.Key, Count = gTemp.Count() };

            // Get the free cabins (except in the cabin booked)
            freeCabins = db.ViewCabinBoats.Where(n => n.BoatID.Equals(boatId) && !result_temp.Select(t => t.CabinID).Contains(n.CabinID));

            return freeCabins;
        }

        public CabinEntity GetCabin(int cabinID)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.CabinEntities.SingleOrDefault(a => a.CabinID == cabinID);
            }
        }

        public void InsertCabins(List<CabinEntity> ls)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.CabinEntities.InsertAllOnSubmit(ls);
                db.SubmitChanges();
            }
        }

        public void UpdateCabin(CabinEntity item)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.UpdateCabin(item.CabinID, item.BoatID, item.CabinNumber, item.Description, item.Floor, item.ForRent, item.MaxGuests, item.Active);
            }
        }

        public void DeleteCabin(string boatID)
        {
            try
            {
                using (OBSDataContext db = new OBSDataContext(base.connectionString))
                {
                    var query = db.CabinEntities.Where(x => x.BoatID == boatID);
                    db.CabinEntities.DeleteAllOnSubmit(query);
                    db.SubmitChanges();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}